1. Field of the Invention
The present invention generally relates to On-Line Analytical Processing (OLAP) computer applications, and more particularly to validating the technical correctness of reporting projects created using such applications.
2. Related Art
In today's advanced technological climate, large enterprises (i.e., educational organizations, institutions, governmental agencies, corporations, other businesses and the like) often employ computer database systems for storing, retrieving and managing large amounts of data generated from, and used during, day-to-day operations (i.e., data warehouses). The data in these data warehouses are then used by the enterprises, for example, to generate reports on which enterprise performance is evaluated and business decisions are based. Consequently, the practice of validating data warehouses is an important discipline. Yet, the available literature addressing testing and validation strategies for data warehouse reporting—an important area within data warehousing—is limited. Rather, the majority of the literature in this field relates to building data warehouses.
OLAP tools are a category of database software which provides an interface to such data warehouses so that users can transform and filter raw data according to user-defined or pre-defined functions, and quickly and interactively examine the results in various dimensions of the data. That is, OLAP databases are capable of being queried by OLAP tools. Thus, OLAP tools primarily aggregate large amounts of diverse data, which can involve millions of data items with complex relationships, and analyze these relationships in order to identify patterns, trends and exceptions. One of the more widely-used OLAP tools is the MicroStrategy 7i OLAP reporting suite, available from MicroStrategy, Inc. of McLean, Va.
So important are OLAP tools to the success of an enterprise's operations, the OLAP Council, a Boston-based nonprofit consortium of industry leaders in the OLAP market was established in January 1995. Its mission is to provide education about OLAP technology for business intelligence applications and to help position OLAP within a broader IT architecture. The OLAP Council also focuses on establishing guidelines for OLAP interoperability and data navigation.
OLAP tools generally employ a Structured Query Language (SQL) generation engine. The SQL generation engine reads a project-specific database called the “project schema.” Within the project schema, all relevant aspects of the project's database environment are defined. A typical OLAP (e.g., MicroStrategy 7i) reporting project can offer hundreds or thousands of dynamic report combinations, and the project schema must be correctly defined to support all of the offered report combinations. Schema designers, however, can inadvertently introduce errors into the project schema. The OLAP tool's SQL generation engine can then read the erroneous schema and produce unexpected SQL, and then the subsequent reporting results can be unexpected and possibly erroneous.
For example, one of the primary capabilities of OLAP tools is the ability for a reporting analyst to modify reports dynamically (e.g., by adding or removing header or data values). This capability allows an analyst to make last minute decisions about what data is most interesting to examine. The reporting analyst would typically run one report, then run another report pertaining to a subset or superset of the data in the original report. Both reports would then be visible and available for a side-by-side comparison. The analyst would expect, of course, that header and data values flow smoothly from the first report to the second (i.e., that the values would not change unexpectedly).
Yet, it has been observed that displayed values can change merely because the reporting analyst modifies a report dynamically (e.g., by simply “dragging on” or “dragging off” a reporting attribute or metric on the OLAP tool's graphical user interface). Visible, unexpected changes of header or data values would certainly be undesirable. The changes in report results would be confusing. It is likely that the visible changes would diminish management's confidence in the quality and usability of the OLAP tool's reporting capability. Even worse, invisible changes or errors (i.e., those embedded and undetected in a report) are known to occur. Such invisible errors can be hazardous to an enterprise. In sum, observed errors include, but are not limited to, erroneous data total changes due to adding (dragging on) a new attribute in a report; erroneous header value changes due to the removal of a metric; and erroneous header value changes due to the addition of a metric.
In general, SQL generation engines are powerful and complex technologies that deserve to be studied systematically, with the study results cataloged for the purpose of system error elimination. Furthermore, the boundaries between the SQL generation engine and peer components (such as the project schema, the data warehouse metadata and the data warehouse data itself) should be examined for vulnerable areas.
The prior art has no consistent, repeatable methodology for verifying the correctness of an OLAP reporting project. Further, it is common to witness repeated occurrences of data quality errors during the generation of OLAP reporting projects. Those charged with debugging those errors often wonder if there might be a way to be proactive (i.e., proactively to prove or disprove the technical “correctness” of any OLAP reporting project).
Given the foregoing, what is needed is a system, method and computer program product for validating the technical correctness of an OLAP reporting project.